package com.ph.irp.base.bom.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.bom.dao.BOMBillDAO;
import com.ph.irp.model.PBaseBombill;
import com.ph.util.DBUtil;

public class BOMBillDAOImpl implements BOMBillDAO {
	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	public List<PBaseBombill> getBOMBillByBOMGroupID(String bomGroupID)
			throws Throwable {
		List<PBaseBombill> resList = new ArrayList<PBaseBombill>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append("select b.BOMBillID bomBillID,b.BOMBillCode bomBillCode,");
		sb.append("b.BOMGroupID bomGroupID,b.MaterialID materialID,");
		sb.append("b.State state,b.AuditState auditState,");
		sb.append("b.AttributeBasicType attBasicType,b.Unit unit,");
		sb.append("b.Number number,b.Fee fee,");
		sb.append("b.Memo memo ");
		sb.append("from p_base_bombill b ");
		sb.append("where BOMGroupID =").append(Integer.parseInt(bomGroupID));
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBaseBombill bomBill = new PBaseBombill();
				bomBill.setBomBillId(rs.getInt("bomBillID"));
				bomBill.setBomBillCode(rs.getString("bomBillCode"));
				bomBill.setBomGroupId(rs.getInt("bomGroupID"));
				bomBill.setVerifyProjectMaterialId(rs.getInt("materialID"));
				bomBill.setState(rs.getInt("state"));
				bomBill.setAuditState(rs.getInt("auditState"));
				bomBill.setAttributeBasicType(rs.getInt("attBasicType"));
				bomBill.setUnit(rs.getString("unit"));
				bomBill.setNumber(rs.getFloat("number"));
				bomBill.setFee(rs.getFloat("fee"));
				bomBill.setMemo(rs.getString("memo"));
				resList.add(bomBill);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

		return resList;
	}

	public PBaseBombill getBOMBillByBOMBillID(String bomBillID)
			throws Throwable {
		PBaseBombill bomBill = new PBaseBombill();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append("select b.BOMBillID bomBillID,b.BOMBillCode bomBillCode,");
		sb.append("b.BOMGroupID bomGroupID,b.MaterialID materialID,");
		sb.append("b.State state,b.AuditState auditState,");
		sb.append("b.AttributeBasicType attBasicType,b.Unit unit,");
		sb.append("b.Number number,b.Fee fee,");
		sb.append("b.Memo memo, ");
		sb.append("b.auditer auditer, ");
		sb.append("b.auditDate auditDate ");
		sb.append("from p_base_bombill b ");
		sb.append("where BOMBillID =").append(Integer.parseInt(bomBillID));

		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				bomBill.setBomBillId(rs.getInt("bomBillID"));
				bomBill.setBomBillCode(rs.getString("bomBillCode"));
				bomBill.setBomGroupId(rs.getInt("bomGroupID"));
				bomBill.setVerifyProjectMaterialId(rs.getInt("materialID"));
				bomBill.setState(rs.getInt("state"));
				bomBill.setAuditState(rs.getInt("auditState"));
				bomBill.setAttributeBasicType(rs.getInt("attBasicType"));
				bomBill.setUnit(rs.getString("unit"));
				bomBill.setNumber(rs.getFloat("number"));
				bomBill.setFee(rs.getFloat("fee"));
				bomBill.setMemo(rs.getString("memo"));
				bomBill.setAuditer(rs.getString("auditer"));
				bomBill.setAuditTime(rs.getTimestamp("auditDate"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

		return bomBill;

	}

	public List<PBaseBombill> getBOMBillByStateAndCode(String bomBillCode,
			String state, String auditState, String bomGroupID)
			throws Throwable {

		List<PBaseBombill> resList = new ArrayList<PBaseBombill>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append("select b.BOMBillID bomBillID,b.BOMBillCode bomBillCode,");
		sb.append("b.BOMGroupID bomGroupID,b.MaterialID materialID,");
		sb.append("b.State state,b.AuditState auditState,");
		sb.append("b.AttributeBasicType attBasicType,b.Unit unit,");
		sb.append("b.Number number,b.Fee fee,");
		sb.append("b.Memo memo ");
		sb.append("from p_base_bombill b ");
		sb.append("where 1=1 ");

		if (bomBillCode != null && !bomBillCode.equals("")) {
			sb.append("and BOMBillCode like '%").append(bomBillCode).append(
					"%' ");
		}

		if (state != null && !state.equals("") && !state.equals("2")) {
			sb.append(" and State=").append(Integer.parseInt(state));
		}

		if (auditState != null && !auditState.equals("")
				&& !auditState.equals("2")) {
			sb.append(" and AuditState=").append(Integer.parseInt(auditState));
		}
		sb.append(" and BOMGroupID=").append(Integer.parseInt(bomGroupID));
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBaseBombill bomBill = new PBaseBombill();
				bomBill.setBomBillId(rs.getInt("bomBillID"));
				bomBill.setBomBillCode(rs.getString("bomBillCode"));
				bomBill.setBomGroupId(rs.getInt("bomGroupID"));
				bomBill.setVerifyProjectMaterialId(rs.getInt("materialID"));
				bomBill.setState(rs.getInt("state"));
				bomBill.setAuditState(rs.getInt("auditState"));
				bomBill.setAttributeBasicType(rs.getInt("attBasicType"));
				bomBill.setUnit(rs.getString("unit"));
				bomBill.setNumber(rs.getFloat("number"));
				bomBill.setFee(rs.getFloat("fee"));
				bomBill.setMemo(rs.getString("memo"));
				resList.add(bomBill);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

		return resList;
	}

	public void addBOMBill(PBaseBombill bomBill) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb
					.append(" insert into p_base_bombill(BOMBillCode,BOMGroupID,MaterialID,");
			sb.append(" State,AuditState,Unit,Number,Fee,Memo)");
			sb.append(" values('");
			sb.append(bomBill.getBomBillCode());
			sb.append("',").append(bomBill.getBomGroupId());
			sb.append(",").append(bomBill.getVerifyProjectMaterialId());
			sb.append(",").append(0);
			sb.append(",").append(0);
			sb.append(",'").append(bomBill.getUnit());
			sb.append("',").append(bomBill.getNumber());
			sb.append(",").append(bomBill.getFee());
			sb.append(",'").append(bomBill.getMemo()).append("'");
			sb.append(")");

			// connection.insert(sb.toString());
			// dataSource.set
			con = dataSource.getConnection();
			con.setAutoCommit(false);
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();

			con.commit();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}
	}

	public void editBOMBill(PBaseBombill bomBill) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append("update p_base_bombill set MaterialID=");
		sb.append(bomBill.getVerifyProjectMaterialId()).append(",unit='");
		sb.append(bomBill.getUnit()).append("',number=");
		sb.append(bomBill.getNumber()).append(",fee=");
		sb.append(bomBill.getFee()).append(",memo='");
		sb.append(bomBill.getMemo()).append("' ");
		sb.append("where BOMBillID=").append(bomBill.getBomBillId());
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());

		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

	}

	public void deleteBOMBill(String deleteBOMBillIDs) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;

		StringBuilder sb = new StringBuilder();
		sb.append(" delete from p_base_bombill ");
		sb.append(" where BOMBillID in (").append(deleteBOMBillIDs).append(")");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, null);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}
	}

	public int getAuditStateByBOMBillID(String useBomBillID) throws Throwable {
		int res = 0;

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select b.AuditState auditState ");
			sb.append("from p_base_bombill b ");
			sb.append("where BOMBillID=")
					.append(Integer.parseInt(useBomBillID));

			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				res = rs.getInt("auditState");
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

		return res;

	}

	public int getStateByBOMBillID(String useBomBillID) throws Throwable {
		int res = 0;

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select b.State state ");
			sb.append("from p_base_bombill b ");
			sb.append("where BOMBillID=")
					.append(Integer.parseInt(useBomBillID));

			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				res = rs.getInt("state");
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

		return res;
	}

	public void setBomBillUsed(String useBomBillID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb.append("update p_base_bombill set State=1 ");
			sb.append("where BOMBillID=")
					.append(Integer.parseInt(useBomBillID));

			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}
	}

	public void setBomBillAntiUsed(String useBomBillID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb.append("update p_base_bombill set State=0 ");
			sb.append("where BOMBillID=")
					.append(Integer.parseInt(useBomBillID));

			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}
	}

	public void setBomBillAudited(String useBomBillID, String auditer,
			String auditDate) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append("update p_base_bombill set AuditState=1, ");
		sb.append(" auditer='").append(auditer).append("',");
		sb.append(" auditDate='").append(auditDate).append("' ");
		sb.append("where BOMBillID=").append(Integer.parseInt(useBomBillID));
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}
	}

	public void setBomBillAntiAudited(String useBomBillID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb.append("update p_base_bombill set AuditState=0, ");
			sb.append("auditer='',");
			sb.append("auditDate=null ");
			sb.append("where BOMBillID=")
					.append(Integer.parseInt(useBomBillID));

			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}
	}

	public String getNewestBomBillCode() throws Throwable {
		String res = null;
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append("select BOMBillCode from p_base_bombill ");
		sb
				.append("where BOMBillID=(select max(BOMBillID) from p_base_bombill)");
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				res = rs.getString("BOMBillCode");
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

		return res;

	}

	public int getBOMBillIdByBOMBillCode(String code) throws Throwable {
		int res = 0;

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select b.BOMBillID bomBillID ");
			sb.append("from p_base_bombill b ");
			sb.append("where BOMBillCode like '%").append(code).append("%'");
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				res = rs.getInt("bomBillID");
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

		return res;
	}

	public boolean isAuditedBOMBill(String bomBillID) throws Throwable {
		boolean flag = false;
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append(" select b.AuditState auditstate from p_base_bombill b  ");
		sb.append(" where b.BOMBillID=").append(bomBillID);
		
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				flag = rs.getInt("auditstate") == 1 ? true : false;
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return flag;

	}
}
